/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package javadbtest;

import java.io.File;
import java.io.IOException;
import java.net.URL;
import java.nio.file.FileVisitResult;
import static java.nio.file.FileVisitResult.CONTINUE;
import java.nio.file.Files;
import java.nio.file.Path;
import java.nio.file.SimpleFileVisitor;
import java.nio.file.attribute.BasicFileAttributes;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ResourceBundle;
import java.util.logging.Level;
import java.util.logging.Logger;
import javafx.event.ActionEvent;
import javafx.fxml.FXML;
import javafx.fxml.Initializable;
import javafx.scene.control.Button;
import javafx.scene.control.Label;
import javafx.scene.control.TableColumn;
import javafx.scene.control.TableView;

/**
 *
 * @author avilja
 */
public class FXMLDocumentController implements Initializable {

    @FXML
    private Label label;
    @FXML
    private TableView<?> tableView;
    @FXML
    private TableColumn<?, ?> idColumn;
    @FXML
    private TableColumn<?, ?> dateColumn;
    @FXML
    private TableColumn<?, ?> authorColumn;
    @FXML
    private TableColumn<?, ?> contentColumn;
    @FXML
    private Button createDummyDataButton;
    @FXML
    private Button deleteAllButton;
    @FXML
    private Button addNewButton;

    private void handleButtonAction(ActionEvent event) {
        System.out.println("You clicked me!");
        label.setText("Hello World!");
    }

    private void destroyDatabase() {
        File file = new File("testdb");
        System.out.println(file.exists());
        try {
            Files.walkFileTree(file.toPath(), new SimpleFileVisitor<Path>() {

                @Override
                public FileVisitResult visitFile(Path file, BasicFileAttributes attrs) throws IOException {

                    //System.out.println("Deleting file: " + file);
                    Files.delete(file);
                    return CONTINUE;
                }

                @Override
                public FileVisitResult postVisitDirectory(Path dir, IOException exc) throws IOException {

                    //System.out.println("Deleting dir: " + dir);
                    if (exc == null) {
                        Files.delete(dir);
                        return CONTINUE;
                    } else {
                        throw exc;
                    }
                }

            });
        } catch (IOException e) {
            e.printStackTrace();
        }

    }

    public void createDatabase() throws Exception {

        destroyDatabase();

        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        try (java.sql.Connection con = DriverManager.getConnection("jdbc:derby:testdb;create=true", "", "")) {
            Statement stmt = con.createStatement();

            //stmt.execute("DROP TABLE USER_NOTE");
            //stmt.execute("DROP TABLE AUTHOR");
            stmt.execute(
                    "CREATE TABLE AUTHOR ("
                    + "ID INT NOT NULL,"
                    + "NAME VARCHAR (30),"
                    + "PRIMARY KEY (ID))"
            );

            stmt.execute(
                    "CREATE TABLE USER_NOTE ("
                    + "ID INT NOT NULL,"
                    + "CREATED TIMESTAMP,"
                    + "EDITED TIMESTAMP,"
                    + "CONTENT LONG VARCHAR,"
                    + "AUTHOR INT NOT NULL,"
                    + "PRIMARY KEY (ID))");
            //+ "FOREIGN KEY (AUTHOR) REFERENCES AUTHOR (ID))");
        }
    }

    private void createDummyData() throws Exception {
        Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        try (java.sql.Connection con = DriverManager.getConnection("jdbc:derby:testdb", "", "")) {
            Statement stmt = con.createStatement();

            stmt.execute("INSERT INTO AUTHOR (ID, NAME) VALUES (1, 'Arttu')");
            stmt.execute("INSERT INTO AUTHOR (ID, NAME) VALUES (2, 'Porsas')");
        }
    }

    @Override
    public void initialize(URL url, ResourceBundle rb) {

        try {
            createDatabase();

        } catch (Exception ex) {
            Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {
            createDummyData();

        } catch (Exception ex) {
            Logger.getLogger(FXMLDocumentController.class.getName()).log(Level.SEVERE, null, ex);
        }

        try {

            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
            java.sql.Connection con = DriverManager.getConnection("jdbc:derby:testdb;", "", "");

            Statement stmt = con.createStatement();

            ResultSet rs = stmt.executeQuery("SELECT * FROM AUTHOR");

            while (rs.next()) {
                int s = rs.getInt("ID");
                String n = rs.getString("NAME");
                System.out.println(s + "   " + n);

            }
        } catch (Exception ex) {
            Logger.getLogger(FXMLDocumentController.class
                    .getName()).log(Level.SEVERE, null, ex);
        }
    }

}
